'use strict';

const Controller = require('egg').Controller;
const moment = require('moment')
class InoutController extends Controller {
  async index() {
    this.ctx.body = 'hi, egg' + moment(new Date(1530001101323)).format('YYYY-MM-DD HH:mm:ss');

  }
  async getInOut() {
    var count = await this.ctx.app.db.all('select count (*) as count from inout')
    let sql = `SELECT o.*,s.name as schoolName 
    FROM inout as o inner join school as s on o.number=s.number where 1=1 `
    let query = this.ctx.query
    let index = query.pageSize * (query.pageIndex - 1)
    if (query.startDate && query.endDate) {
      sql += ` and o.time>= '${query.startDate}' and o.time<= '${query.endDate}' `
      count = await this.ctx.app.db.all(`select count (*) as count from inout where time>= '${query.startDate}' and time<= '${query.endDate}'`)
    }
    if (query.pageSize && query.pageIndex) {
      sql += ` order by o.time desc limit ${query.pageSize} offset ${index} `
    }
    var result = await this.ctx.app.db.all(sql)
    let res = {
      data: result,
      count
    }
    this.ctx.body = res
  }
  async setInOut() {
    const { db } = this.ctx.app;
    const inouts = this.ctx.request.body
    let inout = {}
    for (var key in inouts) {
      if (key == 'time') {
        inout['$' + key] = moment(new Date(inouts[key])).format('YYYY-MM-DD HH:mm:ss')
      } else {
        inout['$' + key] = inouts[key]
      }
    }

    let res = await db.run(`insert into inout (uID,sex,name,fullImage,headImage,cardImage,time,devName,inOut,score,number,type)
    values ($uID,$sex,$name,$fullImage,$headImage,$cardImage,$time,$devName,$inOut,$score,$number,$type)`, inout);
    this.ctx.body = res
  }

  async homeInOut() {
    let query = this.ctx.query
    var count = await this.ctx.app.db.all('select count (*) as count from inout')
    let sql = `SELECT o.*,s.name as schoolName 
    FROM inout as o inner join school as s on o.number=s.number where  o.id>${query.id} order by o.id desc limit 0 , 5 `
    var result = await this.ctx.app.db.all(sql)
    let res = {
      data: result,
      count
    }
    this.ctx.body = res
  }

  async delInOut() {
    const { db } = this.ctx.app;
    const id = this.ctx.request.query.id;
    const res = await db.run(`delete from school where id = ${id}`);
    this.ctx.body = res
  }
}

module.exports = InoutController;
